package servlets;

import com.sun.rowset.CachedRowSetImpl;

import javax.sql.rowset.CachedRowSet;
import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * User: Raz0r
 * Date: 01.12.12
 * Time: 14:04
 * To change this template use File | Settings | File Templates.
 */
public class DBProvider {
    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_CONNECTION = "jdbc:mysql://localhost/KIS";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "1";

    private static Connection getDBConnection() {

        Connection dbConnection = null;

        try {

            Class.forName(DB_DRIVER);

        } catch (ClassNotFoundException e) {

            System.out.println(e.getMessage());

        }

        try {

            dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
                    DB_PASSWORD);
            return dbConnection;

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

        return dbConnection;

    }

    public static ResultSet selectRecordsFromDbUserTable(String selectTableSQL) throws SQLException {

        Connection dbConnection = null;
        Statement statement = null;

        try {
            dbConnection = getDBConnection();
            statement = dbConnection.createStatement();

            System.out.println(selectTableSQL);

            // execute select SQL stetement
            ResultSet rs = statement.executeQuery(selectTableSQL);

            CachedRowSetImpl crs = new CachedRowSetImpl();
            crs.populate(rs);

            return crs;

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            if (statement != null) {
                statement.close();
            }

            if (dbConnection != null) {
                dbConnection.close();
            }

        }

        return null;
    }

    public static long insertRecordIntoTable(String insertTableSQL) throws SQLException {

        Connection dbConnection = null;
        Statement preparedStatement = null;

        /*String insertTableSQL = "INSERT INTO DBUSER"
                + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
                + "(?,?,?,?)";     */

        try {
            dbConnection = getDBConnection();
            preparedStatement = dbConnection.createStatement();//insertTableSQL);
              /*
            preparedStatement.setInt(1, 11);
            preparedStatement.setString(2, "mkyong");
            preparedStatement.setString(3, "system");
            preparedStatement.setTimestamp(4, getCurrentTimeStamp());  */

            // execute insert SQL stetement
            Integer number = preparedStatement.executeUpdate(insertTableSQL, Statement.RETURN_GENERATED_KEYS);

            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()){
                return generatedKeys.getLong(1);
            }

            //return number;
            //System.out.println("Record is inserted into DBUSER table!");

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (dbConnection != null) {
                dbConnection.close();
            }

        }
        return -1;
    }
}
